Option Explicit On

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class Data

    Public Shared Conn As SqlConnection

    Public Shared Function OpenDB() As Boolean

        'Try
        Conn = New SqlConnection
        Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader

        With Conn
            .ConnectionString = CType(configurationAppSettings.GetValue("connectionString", GetType(System.String)), String)
           
            'If .ConnectionString.Contains("gvdb.crmvisas.com") Then
            '    .ConnectionString = .ConnectionString & ";Database=global;User Id=GlobalWeb;Password=0cSn3FKzr9;timeout=30;"
            'End If
            .Open()
        End With

        Return True

        'Catch ex As Exception

        '    If Not Conn Is Nothing Then
        '        Conn.Dispose()
        '    End If

        '    Return False

        'End Try

    End Function

    Public Shared Function CloseDB() As Boolean

        If Not Conn Is Nothing Then
            Conn.Close()
            Return True
        End If

    End Function

    Public Shared Function GetScalar(ByVal SQL As String) As Object

        OpenDB()

        Dim Cmd As New SqlCommand(SQL, Conn)
        Dim ScalarCol As Object

        Try
            ScalarCol = Cmd.ExecuteScalar()
            Return ScalarCol
        Catch ex As Exception
            Return Nothing
        Finally
            Cmd.Dispose()
            CloseDB()
        End Try

    End Function

    Public Shared Function GetQueryResults(ByVal SQL As String, Optional ByVal AddBlankRow As Boolean = False) As DataTable

        OpenDB()

        Dim Cmd As New SqlCommand(SQL, Conn)
        Dim Adapt As New SqlDataAdapter
        Dim Ds As New DataSet

        GetQueryResults = Nothing

        'Try

        Adapt.SelectCommand = Cmd
        Adapt.Fill(Ds)

        If Ds.Tables.Count > 0 Then

            If AddBlankRow Then
                Dim BlankRow As DataRow

                BlankRow = Ds.Tables(0).NewRow
                BlankRow.Item(0) = 0
                BlankRow.Item(1) = ""

                Ds.Tables(0).Rows.InsertAt(BlankRow, 0)
            End If

            Return Ds.Tables(0)
        End If

        'Catch ex As Exception
        '    CRMSession.LastException = ex
        '    HttpContext.Current.Response.Redirect("../../errorpage.aspx")
        'Finally
        '    Ds.Dispose()
        '    Adapt.Dispose()
        '    CloseDB()
        'End Try


    End Function

    'Public Shared Function GetDataTable(ByVal SQL As String) As DataTable

    '    OpenDB()
    '    Return Data.GetQueryResults(SQL)
    '    CloseDB()

    'End Function





End Class
